跳到主要内容

MySQL 常见场景题

SQL1 查找最晚入职员工的所有信息​

SQL 脚本

有一个员工 employees 表简况如下：

查找 employees 里最晚入职员工的所有信息，以上例子输出如下：

-- 第一次的答案
select * from employees order by hire_date desc limit 1;

错误分析，最晚入职的当天未必就一个人，也许有多人，使用排序并限制得只能取得指定数量的结果，所以这里使用 limit 限制只有一个是不对的

所以应该使用这种方式：

select *
from employees
where hire_date = (
    select max(hire_date)
    from employees
);

SQL2 查找入职员工时间排名倒数第三的员工所有信息​

沿用上面的表

请你查找 employees 里入职员工时间排名倒数第三的员工所有信息，以上例子输出如下：

第一次答案的思路是先取得倒数3个数据，再正排取第一个

-- 第一次的答案
select *
from (select * from employees order by hire_date desc limit 3) as tmp
order by tmp.hire_date asc
limit 1;

补充一下 limit 的两个参数：

  • offset：要显示的条目起始索引（从0开始）
  • size：要显示的条目个数

考虑到入职日期可能会有多个重复，而且可以使用分页参数，最合理的 SQL 语句应为：

SELECT *
FROM employees
WHERE hire_date = (
    SELECT hire_date
    FROM employees
    ORDER BY hire_date DESC
    limit 2,1
);

SQL3 查找当前薪水详情以及部门编号 dept_no​

SQL 脚本

有一个全部员工的薪水表 salaries 简况如下：

mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10002 |  72527 | 2001-08-02 | 9999-01-01 |
...

有一个各个部门的领导表 dept_manager 简况如下：

mysql> select * from dept_manager;
+---------+--------+------------+
| dept_no | emp_no | to_date    |
+---------+--------+------------+
| d001    |  10002 | 9999-01-01 |
| d004    |  10004 | 9999-01-01 |
| d003    |  10005 | 9999-01-01 |
| d002    |  10006 | 9999-01-01 |
+---------+--------+------------+

请你查找各个部门当前领导的薪水详情以及其对应部门编号 dept_no，输出结果以 salaries.emp_no 升序排序，并且请注意输出结果里面 dept_no 列是最后一列，以上例子输出如下：

这里的坑主要在于两个表的逻辑关系，题目要求是薪水情况以及部门编号，再结合输出情况 dept_no 被放到了最后一列，可见是主表是“salaries”。这里顺序错了就会提示：您的代码无法通过所有用例

select s.*, d.dept_no
from salaries as s
         join dept_manager as d
              on s.emp_no = d.emp_no
where s.to_date = '9999-01-01'
  and d.to_date = '9999-01-01';

关于为什么一定要两个表格的时间都限制成规定时间（9999-01-01）呢？

因为薪水表是按年发的，而题目要查找的是当前的薪水，所以要过滤掉以前，而 dept_manager 是因为有领导会离职，to_date 时间不一定是 9999-01-01，所以要过滤过离职的领导

SQL5 查找所有员工的 last_name 和 first_name 以及 dept_no​

SQL 脚本

有一个员工表，employees 简况如下:

mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
+--------+------------+------------+-----------+--------+------------+

有一个部门表，dept_emp 简况如下:

mysql> select * from dept_emp;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  10001 | d001    | 1986-06-26 | 9999-01-01 |
|  10002 | d002    | 1996-08-03 | 9999-01-01 |
+--------+---------+------------+------------+

请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工，以上例子如下：

就是左查询

select e.last_name, e.first_name, de.dept_no
from employees e
left join dept_emp de on e.emp_no = de.emp_no;

SQL7 查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t​

SQL 脚本

有一个薪水表，salaries 简况如下:

mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
....

请你查找薪水记录超过 15 次的员工号 emp_no 以及其对应的记录次数 t，以上例子输出如下:

第一次编写

select *
from (
         select emp_no, count(*) as t
         from salaries
         group by emp_no
     ) as tmp
where t > 15;

修改

SELECT emp_no, count(emp_no) as t
from salaries
group by emp_no
having t > 15;

考查知识：having

SQL8 找出所有员工当前薪水salary情况​

有一个薪水表，salaries 如上

请你找出所有员工具体的薪水 salary 情况，对于相同的薪水只显示一次，并按照逆序显示，以上例子输出如下：

第一次提交

select distinct salary from salaries order by salary desc;

考察知识 distinct 关键字（去重）

注意：大表一般用 distinct 效率不高，大数据量的时候都禁止用 distinct，建议用 group by 解决重复问题。

对于 distinct 与 group by 的使用： 1、当对系统的性能高并数据量大时使用 group by 2、当对系统的性能不高时使用数据量少时两者皆可 3、尽量使用 group by

select salary
from salaries
where to_date = '9999-01-01' -- 可以不加
group by salary
order by salary desc

SQL10 获取所有非 manager 的员工 emp_no​

有一个员工表 employees 如上 有一个部门领导表 dept_manager 如上

请你找出所有非部门领导的员工 emp_no，以上例子输出：

第一次提交

select employees.emp_no from employees
left join dept_manager dm on employees.emp_no = dm.emp_no
where dept_no is null;

第二次提交

-- 可以使用 NOT IN 关键字
SELECT emp_no
FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)

SQL11 获取所有员工当前的 manager​

有一个员工表 dept_emp 如上，有一个部门经理表 dept_manager 如上

mysql> select * from dept_manager;
+---------+--------+------------+
| dept_no | emp_no | to_date    |
+---------+--------+------------+
| d001    |  10002 | 9999-01-01 |
| d004    |  10004 | 9999-01-01 |
| d003    |  10005 | 9999-01-01 |
| d002    |  10006 | 9999-01-01 |
+---------+--------+------------+

第一行表示为 d001 部门的经理是编号为 10002 的员工。

mysql> select * from dept_emp;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  10001 | d001    | 1986-06-26 | 9999-01-01 |
|  10002 | d002    | 1996-08-03 | 9999-01-01 |
+--------+---------+------------+------------+

第一行表示为员工编号为 10001 的部门是 d001 部门。

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示，如下：

SELECT e.emp_no, m.emp_no AS manager
FROM dept_emp e LEFT JOIN dept_manager m 
ON e.dept_no = m.dept_no
WHERE e.emp_no <> m.emp_no

SQL12 获取每个部门中当前员工薪水最高的相关信息（困难）​

有一个员工表 dept_emp 简况如上 有一个薪水表 salaries 简况如上

获取每个部门中当前员工薪水最高的相关信息，给出 dept_no, emp_no 以及其对应的 salary，按照部门编号升序排列，以上例子输出如下：

此题常见漏洞：

  1. emp_no 直接和 group by dept_no 一起使用，拿到了最大 salary 但是存在 emp_no 取值其实与 salary 不匹配的问题；
  2. 先使用 group by 获得最高 salary，再去用最高 salary 匹配两表返回 dept_no,emp_no 信息，这存在 A 部门的最高薪水，等于 B 部门非最高薪水时，B 部门的非最高薪水也会被显示出来。
-- 解法一：如果同部门有多条同等最大 salary，一起显示出来
select r.dept_no, ss.emp_no, r.maxSalary
from (
         select d.dept_no, max(s.salary) as maxSalary
         from dept_emp d,
              salaries s
         where d.emp_no = s.emp_no
         group by d.dept_no
     ) as r,
     salaries ss,
     dept_emp dd
where r.maxSalary = ss.salary
  and r.dept_no = dd.dept_no
  and dd.emp_no = ss.emp_no
order by r.dept_no asc;

-- 解法一也可以写成 join 的形式
select result.dept_no, ss.emp_no, result.maxSalary
from dept_emp as dd,
     salaries as ss,
     (
        select dept_no, max(s.salary) as maxSalary
        from dept_emp
                join salaries s on dept_emp.emp_no = s.emp_no
        group by dept_emp.dept_no
    ) as result
where dd.dept_no = result.dept_no
  and dd.emp_no = ss.emp_no
  and ss.salary = result.maxSalary
order by result.dept_no asc;


--  解法二：（如果同部门有多条同等最大salary，仅显示一条）
select r.dept_no, r.emp_no, max(r.salary)
from (
         select d.dept_no, d.emp_no, s.salary
         from dept_emp d,
              salaries s
         where d.emp_no = s.emp_no
         order by s.salary desc
     ) as r
group by r.dept_no
order by r.dept_no asc;

SQL15 查找 employees 表 emp_no 与 last_name 的员工信息（位运算）​

有一个员工表 employees 简况如上

请你查找 employees 表所有 emp_no 为奇数，且 last_name 不为 Mary 的员工信息，并按照 hire_date 逆序排列，以上例子查询结果如下：

select *
from employees
where emp_no % 2 = 1
and last_name != 'Mary'
order by hire_date desc;

这题主要考察 SQL 的奇偶判断

补充知识：

-- 获取偶数的方法
-- 适用于Mysq
select * from pos_info_report_tmp_20110712 r
where mod(r.id,2) = 0;

-- 获取奇数的方法
select * from pos_info_report_tmp_20110712 r
where mod(r.id,2) = 1;


-- 或者直接
-- 适用于 Mysql、SQL Server
SELECT * FROM JIOUSHU WHERE ID % 2 != 0;

SQL16 统计出当前各个 title 类型对应的员工当前薪水对应的平均工资​

使用的 SQL 脚本

有一个员工职称表 titles 简况如下：

有一个薪水表 salaries 简况如下：

mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
...

请你统计出各个 title 类型对应的员工薪水对应的平均工资 avg。结果给出 title 以及平均工资 avg，并且以 avg 升序排序，以上例子输出如下：

select t.title, avg(s.salary)
from salaries as s
         inner join titles t on s.emp_no = t.emp_no
group by t.title;

SQL17 获取当前薪水第二多的员工的 emp_no 以及其对应的薪水 salary​

有一个薪水表 salaries 简况如下:

mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
...

请你获取薪水第二多的员工的 emp_no 以及其对应的薪水 salary

这题和上面的第二题有点像：

select emp_no, salary
from salaries
where salary = (
    (
        select salary
        from salaries
        group by salary
        order by salary desc
        limit 1,1
    )
);

SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary​

mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
...

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用 order by 完成，以上例子输出为:

因为不能使用聚合函数，所以不能像上一题那样做，这里的核心思路是因为是求倒数第二，所以使用两次聚合函数

select ss.emp_no, ss.salary, ee.last_name, ee.first_name
from salaries ss
         join employees ee on ss.emp_no = ee.emp_no
where salary = (
    (
        select max(salary)
        from salaries
        where salary < (select max(salary)
                                 from salaries)
    )
);

SQL19 查找所有员工的last_name和first_name以及对应的dept_name​

使用的 SQL

有一个员工表 employees 简况如上

有一个部门员工关系表 dept_emp 简况如上

有一个部门表 departments 表简况如下：

请你查找所有员工的 last_name 和 first_name 以及对应的 dept_name，也包括暂时没有分配部门的员工，以上例子输出如下：

因为也要打印没有分配部门，所以使用两次 left join

select last_name, first_name, dept_name from employees
left join dept_emp de on employees.emp_no = de.emp_no
left join departments d on de.dept_no = d.dept_no;